package com.taxsearch.controller.room;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


import com.taxsearch.util.roomutil.PropertyUtil;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

/**
 * @description: 
 *
 * @author: 杜一平
 *
 * @create: 2018年9月30日-上午11:12:37
 **/
public class GongAnDB {
	// 日志
	public static final Logger logger = LogManager
			.getLogger(PropertyUtil.class);
	private static Connection connection = null;
	private static PreparedStatement smt = null;
	private static ResultSet rs = null;
	
	//公安入住数据库
	public static Connection getConnection() {
		String USERNAMR = PropertyUtil.getProperty("jdbc_username").trim();
		String PASSWORD = PropertyUtil.getProperty("jdbc_password").trim();
		String DRVIER = PropertyUtil.getProperty("jdbc_driverClassName").trim();
		String URL = PropertyUtil.getProperty("jdbc_url").trim();
		try {
			Class.forName(DRVIER);
			connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
			logger.info("成功连接数据库");
		} catch (ClassNotFoundException e) {
			logger.info("ClassNotFound");
		} catch (SQLException e) {
			logger.info("连接分发库失败");
		}
		return connection;
	}
	/**
	 * 
	 * @Title: close
	 * @Description: 关闭连接
	 * @param:
	 * @return: void
	 * @throws
	 */
	public static void close() {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				logger.error("关闭数据库连接过程中失败，捕捉到SQLException", e);
			}
		}
		if (smt != null) {
			try {
				smt.close();
			} catch (SQLException e) {
				logger.error("关闭数据库连接过程中失败，捕捉到SQLException", e);
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				logger.error("关闭数据库连接过程中失败，捕捉到SQLException", e);
			}
		}
	}
	
	// 更新入住状态，拟0无证审批中，1有证，2成功，3失败
	public static void updateRuzhuStatus(Connection connection,List<String>list,String status) throws SQLException{
		if(list.size()!=0){
			String idsString="";
			for(int i=0;i<list.size();i++){
				if(i==0){
					idsString+="'"+list.get(i)+"'";
				}else{
					idsString+=", '"+list.get(i)+"'";
				}
			}
			// 执行更新
			String sql ="update YPT_CGUESTER set ENTERPRISE_ID='"+status+"' where RECORDSNO in("+idsString+")";
			smt=connection.prepareStatement(sql);
			rs = smt.executeQuery();
		}
	}
	
	
	public static List<Map<String, String>> selectWuzhengFromRuzhu(Connection connection ,String hotelId) throws SQLException {
		
		String sql ="SELECT T.DJ_XINGMING, T.ENTERPRISE_ID,T.DJ_RUZHUFANGHAO FROM  YPT_CGUESTER T WHERE T.STATIONID ='"+hotelId+"'"+
					"AND NVL(T.DJ_TUIFANGSHIJIAN ,TO_DATE('2000-01','yyyy-mm'))=TO_DATE('2000-01','yyyy-mm')"+
					"AND ENTERPRISE_ID IN ('0','2','3','4','5')";
		smt=connection.prepareStatement(sql);
		rs = smt.executeQuery();
		List<Map<String, String>>list=new LinkedList<>();
		while(rs.next()){
			Map<String, String>map=new HashMap<>();
			map.put("DJ_XINGMING", rs.getString("DJ_XINGMING"));
			map.put("ENTERPRISE_ID", rs.getString("ENTERPRISE_ID"));
			map.put("DJ_RUZHUFANGHAO", rs.getString("DJ_RUZHUFANGHAO"));
			list.add(map);
		}
		return list;
	}
		
}
